package com.xiaouyudeguang.common.handler;

import com.xiaouyudeguang.common.entity.Column;
import com.xiaouyudeguang.common.entity.Table;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Component
public class TableHandler {

    private static final List<String> tableExceptList = new ArrayList<>();

    private static DataSource dataSource;

    public TableHandler(@Autowired DataSource dataSource) {
        TableHandler.dataSource = dataSource;
    }

    public static String tableSQL(String tableName, String tableAnnotation) {
        return "CREATE TABLE `" + tableName + "`  (\n" +
                "  `id` bigint NOT NULL primary key comment '主键id' ,\n" +
                "  `created` datetime(0) NULL DEFAULT NULL comment '创建时间',\n" +
                "  `creator` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL comment '创建人',\n" +
                "  `modified` datetime(0) NULL DEFAULT NULL comment '修改时间',\n" +
                "  `modifier` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL comment '修改人',\n" +
                "  `status` bigint NULL DEFAULT 0 comment '状态'\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '" + tableAnnotation + "' ROW_FORMAT = Dynamic;";
    }

    public static void createTableSQL(String tableName, String tableAnnotation) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.createStatement().execute(tableSQL(tableName, tableAnnotation));
        } catch (Exception ignored) {
        } finally {
            try {
                assert conn != null;
                conn.close();
            } catch (SQLException ignored) {
            }
        }
    }

    public void addExceptTable(String tableName) {
        tableExceptList.add(tableName);
    }

    private static List<Table> queryDataTables() {
        Connection conn = null;
        List<Table> tables = new ArrayList<>();
        try {
            conn = dataSource.getConnection();
            DatabaseMetaData dbmd = conn.getMetaData();
            ResultSet resultSet = dbmd.getTables(null, null, null, new String[]{"TABLE"});
            while (resultSet.next()) {
                Table table = new Table();
                table.setColumns(new ArrayList<Column>());
                String tableName = resultSet.getString("TABLE_NAME");
                String remarkes = getCommentByTableName(tableName);
                table.setTableName(tableName);
                table.setComment(remarkes);
                ResultSet rs = dbmd.getColumns(null, "%", tableName, "%");
                while (rs.next()) {
                    Column column = new Column(rs.getString("COLUMN_NAME"), rs.getString("REMARKS"), rs.getString("TYPE_NAME"));
                    table.getColumns().add(column);
                }
                tables.add(table);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tables;
    }

    public static List<Table> getTables() {
        List<Table> dataTableList = queryDataTables();
        if (dataTableList.size() != 0) {
            if (null != tableExceptList && tableExceptList.size() > 0) {
                for (String tableName : tableExceptList) {
                    dataTableList.removeIf(x -> x.getTableName().equals(tableName));
                }
            }
        }
        return dataTableList;
    }

    private static String getCommentByTableName(String tableName) throws Exception {
        Connection conn = dataSource.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
        String comment = null;
        if (rs != null && rs.next()) {
            comment = parse(rs.getString(2));
        }
        rs.close();
        stmt.close();
        conn.close();
        return comment;
    }

    /**
     * 返回注释信息
     *
     * @param all
     * @return
     */
    private static String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }
}